/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.TourOder;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.DBUtil;

/**
 *
 * @author lab
 */
public class TourOrderDAO {

    String SQL_CREATE_TOURORDER = "INSERT INTO [TourOder](TourID, CustomerID, StartDate, OrderDate, Notes, Status) VALUES(?, ?, ?, ?, ?, ?)";
    String SQL_READ_ALL_TOURORDER = "SELECT * FROM [TourOder]";
    String SQL_UPDATE_TOURORDER = "UPDATE [TourOder] SET TourID =?, CustomerID = ?,StartDate = ?, OrderDate = ?, Notes = ?, Status = ? WHERE OrderID = ? ";
    String SQl_DELETE_TOURORDER = "DELETE FROM [TourOder] WHERE OderID = ?";
    String SQL_GET_TOURORDER_BY_TOURORDERID = "SELECT * FROM WHERE OderID = ?";
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    public String result = "";

    public TourOrderDAO() {
    }

    public String createTourOrder(TourOder tourOrder) {
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_CREATE_TOURORDER);
            pst.setInt(1, tourOrder.getTour().getTourID());
            pst.setInt(2, tourOrder.getCustomer().getCustomerID());
            pst.setDate(3, tourOrder.getStartDate());
            pst.setDate(4, tourOrder.getOrderDate());
            pst.setString(5, tourOrder.getNotes());
            pst.setBoolean(6, tourOrder.isStatus());
            if (pst.executeUpdate() > 0) {
                result = "Create Tour Order successfully!";
            } else {
                result = "Create Tour Order unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourOrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }
    public String updateTourOrder(TourOder tourOrder)
    {
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_UPDATE_TOURORDER);
            pst.setInt(1, tourOrder.getTour().getTourID());
            pst.setInt(2, tourOrder.getCustomer().getCustomerID());
            pst.setDate(3, tourOrder.getStartDate());
            pst.setDate(4, tourOrder.getOrderDate());
            pst.setString(5, tourOrder.getNotes());
            pst.setBoolean(6, tourOrder.isStatus());
            pst.setInt(7, tourOrder.getOrderID());
            if (pst.executeUpdate() > 0) {
                result = "Update Tour Order successfully!";
            } else {
                result = "Update Tour Order unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourOrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }
    public String deleteTourOrder(int orderID)
    {
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQl_DELETE_TOURORDER);
            pst.setInt(1, orderID);
            if (pst.executeUpdate() > 0) {
                result = "Delete order successfully!";
            } else {
                result = "Delete order unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }
    public ArrayList<TourOder> getAllOrder() {
        ArrayList<TourOder> orders = new ArrayList<TourOder>();
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_READ_ALL_TOURORDER);
            rs = pst.executeQuery();
            while (rs.next()) {
                TourOder tourOrder = new TourOder();
                tourOrder.setOrderID(rs.getInt("OrderID"));
                int tourID = rs.getInt("TourID");
                TourDAO tourDAO = new TourDAO();
                tourOrder.setTour(tourDAO.getTour(tourID));
                int customerID = rs.getInt("CustomerID");
                CustomerDAO cusDAO = new CustomerDAO();
                tourOrder.setCustomer(cusDAO.getCustomerByID(customerID));
                tourOrder.setStartDate(rs.getDate("StartDate"));
                tourOrder.setOrderDate(rs.getDate("OrderDate"));
                tourOrder.setNotes(rs.getString("Notes"));
                tourOrder.setStatus(rs.getBoolean("Status"));
                orders.add(tourOrder);
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourOrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return orders;
    }
    public TourOder getOrderByID(int orderID) {
        TourOder order = null;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_GET_TOURORDER_BY_TOURORDERID);
            pst.setInt(1, orderID);
            rs = pst.executeQuery();
            while (rs.next()) {
                order = new TourOder();
                order.setOrderID(rs.getInt("OrderID"));
                int tourID = rs.getInt("TourID");
                TourDAO tourDAO = new TourDAO();
                order.setTour(tourDAO.getTour(tourID));
                int customerID = rs.getInt("CustomerID");
                CustomerDAO cusDAO = new CustomerDAO();
                order.setCustomer(cusDAO.getCustomerByID(customerID));
                order.setStartDate(rs.getDate("StartDate"));
                order.setOrderDate(rs.getDate("OrderDate"));
                order.setNotes(rs.getString("Notes"));
                order.setStatus(rs.getBoolean("Status"));
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourOrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return order;
    }
}
